﻿#include"XQMySql.hpp"
//#include"XQFuncEventFilter.hpp"
//#include"XQFuncEvent.h"
#include"XQDatabaseData.h"
#include"XQAlgorithm.h"
#include"XQLog.hpp"
#include<QSqlRecord>
#include<QSqlError>
#include<QThread>
#include<QApplication>
#include<QDateTime>
#include<QDebug>
XQMySql* XQMySql::m_mysql = nullptr;
XQLog* XQMySql::m_log=XQLog::Create(XQMysqlInfo.key(),LogType::Info, /*XQLog::cmd_out|*/ XQLog::cmd_cpp | XQLog::showLogType|XQLog::cmd_queue);//mysql全局日志
XQMySql::XQMySql(QObject* parent)
    :QObject(parent)
{
    init();
}
XQMySql::XQMySql(QString SqlName, QString host, quint16 port, QString name, QString password, QString DataBase, QObject* parent)
    :QObject(parent)
{
    connection(SqlName,host,port,name,password, DataBase);
    init();
}

XQMySql::XQMySql(QString host, quint16 port, QString name, QString password, QString DataBase, QObject* parent)
    :QObject(parent)
{
    connection(QString(), host, port, name, password, DataBase);
    init();
}

XQMySql::~XQMySql()
{
    SqlQuery().finish();
    SqlQuery().clear();
    database().rollback();
    database().close();
    QSqlDatabase::removeDatabase(m_SqlName);
}
XQMySql* XQMySql::globalInstance()
{
    return m_mysql;
}
XQMySql* XQMySql::globalInstance(QString host, quint16 port, QString name, QString password, QString DataBase)
{
    if (m_mysql != nullptr)
        m_mysql->deleteLater();
    m_mysql = new XQMySql(host,port,name,password, DataBase);
    return m_mysql;
}
XQMySql* XQMySql::cloneDatabase(const QString& connectionName)
{
    QString name = connectionName + "<--->" + QString::number(random(0, 99999999)) + QString::number(QDateTime::currentMSecsSinceEpoch());
    QSqlDatabase database = QSqlDatabase::cloneDatabase(this->m_SqlName, name);
    if(!openDatabase(database,reconnectionCount(), sleepTime()))
    {
        emit sqlError(name + ":打开数据库失败了,error:"+ database.lastError().databaseText());
        return nullptr;
    }
    auto mysql = new XQMySql();
    mysql->m_SqlName = name;
    mysql->m_sqlQuery= QSqlQuery(database);
    return mysql;
}
QScopedPointer<XQMySql> XQMySql::cloneDatabase_ScopedPointer(const QString& connectionName)
{
    return QScopedPointer<XQMySql>(cloneDatabase(connectionName));
}
QSharedPointer<XQMySql> XQMySql::cloneDatabase_SharedPointer(const QString& connectionName)
{
    return QSharedPointer<XQMySql>(cloneDatabase(connectionName));
}
void XQMySql::connection(QString SqlName, QString host, quint16 port, QString name, QString password, QString DataBase)
{
    this->m_SqlName = SqlName;
    QSqlDatabase database;
    if(!m_SqlName.isEmpty())
        database =(QSqlDatabase::addDatabase("QMYSQL", m_SqlName));
    else
        database = (QSqlDatabase::addDatabase("QMYSQL"));
    m_hostPort[host] = port;
    database.setHostName(host);
    database.setPort(port);
    database.setUserName(name);
    database.setPassword(password);
    database.setDatabaseName(DataBase);
    database.setConnectOptions("MYSQL_OPT_RECONNECT=1");//设置自动重连
    if (!openDatabase(database, reconnectionCount(), sleepTime()))
    {
        emit sqlError(database.lastError().databaseText());
        return;
    }
    m_sqlQuery = QSqlQuery(database);
    //new XQFuncEvent(this, [this] {  emit connectionSucceed(); });
    emit connectionSucceed();
}
void XQMySql::addHostPort(QString host, quint16 port)
{
    if (host.isEmpty())
        return;
    m_hostPort[host] = port;
}
void XQMySql::removeHostPort(QString host)
{
    m_hostPort.remove(host);
}
void XQMySql::setReconnectionCount(int count)
{
    m_reconnectionCount = count;
}
void XQMySql::setSleepTime(int sleep)
{
    m_sleepTime = sleep;
}
bool XQMySql::isOpen() const
{
    return database().isOpen();
}
QSqlQuery& XQMySql::SqlQuery()
{
    return m_sqlQuery;
}
QSqlDatabase XQMySql::database()const
{
    if (!m_SqlName.isEmpty())
        return QSqlDatabase::database(m_SqlName);
    return std::move(QSqlDatabase::database());
}
bool XQMySql::Query(const QString& Sql, const QVariantList& values)
{
    if (!database().isOpen() )
    {
        emit sqlError(error());
        return false;
    }
    QString sql = Sql;
    if (sql.back() != ';')
        sql += ";";
    QSqlQuery& query = SqlQuery();
    query.prepare(sql);
    for (auto& value : values)
    {
        query.addBindValue(value);
    }
    if (!query.exec())
        return false;
    return true;
}
bool XQMySql::createTable(XQDatabaseData* database, const QString& tableName)
{
    return Query(database->createTableSql(tableName));
}
bool XQMySql::deleteTable(const QString& tableName)
{
    return Query(QString("DROP TABLE IF EXISTS `%1`;").arg(tableName));
}
QString XQMySql::uuid(const QString& variate)
{
    if (variate.isEmpty())
    {
        Query("SELECT replace(uuid(), '-', '');");
    }
    else
    {
        Query(QString("SET %1 :=(SELECT replace(uuid(), '-', ''));").arg(variate));
        Query(QString("SELECT %1;").arg(variate));
    }
    return  result_one_toString();
}
QDateTime XQMySql::ctime(const QString& variate)
{
    if (variate.isEmpty())
    {
        Query("SELECT NOW();");
    }
    else
    {
        Query(QString("SET %1 :=(NOW());").arg(variate));
        Query(QString("SELECT %1;").arg(variate));
    }
    return result_one().toDateTime();
}
bool XQMySql::result()
{
    if (SqlQuery().next())
        return true;
    return false;
}
bool XQMySql::result(QVariant& ret, int nSel)
{
    auto& query = SqlQuery();
    if (!query.next())
        return false;
    auto recordCnt = query.record().count();//获取列数
    if(recordCnt> nSel)
    {
        ret = query.value(nSel);
        return true;
    }
    return false;
}
bool XQMySql::result(QVariantList& ret)
{
    auto& query = SqlQuery();
    if(!query.next())
        return false;
    auto recordCnt = query.record().count();//获取列数
    for (int i = 0; i < recordCnt; i++)
    {
        ret.append(query.value(i));
    }
    return true;
}
bool XQMySql::result(QStack<QVariant>& ret)
{
   auto list=result_all();
    if(list.isEmpty())
        return false;
    for (auto& item:list)
    {
        ret.append(std::move(item[0]));
    }
    return true;
}
bool XQMySql::SELECT(const QString& tableName, const QStringList& fields, const QString& rightSql,const QVariantList& values)
{
    if (tableName.isEmpty() || fields.isEmpty())
        return false;
    QString sql = QString("SELECT ");
    for (auto& field : fields)
    {//设置字段
        sql += QString("`%1`,").arg(field);
    }
    sql.remove(sql.size() - 1, 1);//删除最后一个逗号
    sql += QString(" FROM `%1` ").arg(tableName);
    if (!rightSql.isEmpty())
        sql += rightSql;
    sql = sql.trimmed();
    if(sql.back()!=';')
        sql += ";";
    return Query(sql,values);
}
bool XQMySql::SELECT_Where(const QString& tableName, const QStringList& fields, const QString& Where , const QVariantList& values )
{
    if (tableName.isEmpty() || fields.isEmpty())
        return false;
    QString condition;
   if (!Where.isEmpty())
   {
       if (!Where.contains("WHERE"))
           condition += "WHERE ";
       condition += Where;
   }
    return SELECT(tableName,fields, condition,values);
}
bool XQMySql::SELECT_Where(const QString& tableName, const QStringList& fields, XQDatabaseData* receive, const QString& Where, const QVariantList& WhereBindValues)
{
    if (receive == nullptr)
        return false;
    if (!SELECT_Where(tableName, fields, Where, WhereBindValues))
        return false;
    auto data = result_oneRow();
    if (data.isEmpty())
        return false;
    receive->setDatas(data,false, fields);
    return true;
}
QStringList XQMySql::fieldList(const QString& tableName)
{
    auto record = database().record(tableName);
    QStringList fields;//字段列表
    for (int i = 0; i < record.count(); i++)
    {
        fields << record.fieldName(i);
    }
    return std::move(fields);
}
bool XQMySql::INSERT(const QString& tableName, const QStringList& fields,const QVariantList& datas)
{
    return INSERTS(tableName, fields, {datas});
}
bool XQMySql::INSERT(const QString& tableName, const QVariantMap& data)
{
    return INSERT(tableName,data.keys(),data.values());
}
bool XQMySql::INSERT(const QString& tableName, const QStringList& fields, XQDatabaseData* data)
{
    if(data)
        return INSERT(tableName,data->toVariantMap(false,fields));
    return false;
}
bool XQMySql::INSERTS(const QString& tableName, const QStringList& fields, const QList<QVariantList>& datas)
{
    if (tableName.isEmpty() || fields.isEmpty() || datas.isEmpty()||fields.size() != datas[0].size())
        return false;
    QString sql = QString("INSERT INTO `%1` (").arg(tableName);
    for (auto& field : fields)
    {//设置字段
        sql += QString("`%1`,").arg(field);
    }
    sql.remove(sql.size() - 1, 1);//删除最后一个逗号
    sql += ") VALUES ";
    for (size_t i = 0; i < datas.size(); i++)
    {
        sql += "(";
        for (auto& field : fields)
        {//设置占位符号
            sql += QString("?,");
        }
        sql.remove(sql.size() - 1, 1);//删除最后一个逗号
        sql += "),";
    }
    sql.remove(sql.size() - 1, 1);//删除最后一个逗号
    sql += ";";

    QSqlQuery& query = SqlQuery();
    query.prepare(sql);
    for (auto& dataRow: datas)
    {
        for (auto& data : dataRow)
        {
            query.addBindValue(data);
        }
    }

    if (!query.exec())
        return false;
    return true;
}
bool XQMySql::INSERTS(const QString& tableName, const QStringList& fields, const QList<XQDatabaseData*>& insertDatas)
{
    if (insertDatas.isEmpty())
        return false;
    
    QList<QVariantList> datas;
    for (auto&database:insertDatas)
    {
        if (database == nullptr)
            continue;
        datas << database->toVariantList(false,fields);
    }
    if (fields.isEmpty())
        return false;
    return INSERTS(tableName, fields, datas);
}
bool XQMySql::UPDATE(const QString& tableName, const QStringList& fields,const QVariantList& insertDatas, const QString& Where, const QVariantList& WhereBindValues )
{
    if (tableName.isEmpty() || fields.isEmpty() || Where.isEmpty() || fields.size() != insertDatas.size())
        return false;
    QString sql = QString("UPDATE `%1` SET ").arg(tableName);
    for (auto& field : fields)
    {
        sql += QString("`%1`=?,").arg(field);
    }
    sql.remove(sql.size() - 1, 1);//删除最后一个逗号
    sql += " ";
    if (!Where.isEmpty())
    {
        if (!Where.contains("WHERE"))
            sql += "WHERE ";
        sql += Where;
    }
    sql = sql.trimmed();
    if (sql.back() != ';')
        sql += ";";
    /*sql += Where; sql += ";";*/
    auto data = insertDatas;
    data.append(WhereBindValues);
    return Query(sql,data);
}
bool XQMySql::UPDATE(const QString& tableName, const QVariantMap& data, const QString& Where , const QVariantList& WhereBindValues )
{
    return UPDATE(tableName,data.keys(),data.values(),Where,WhereBindValues);
}
bool XQMySql::UPDATE(const QString& tableName, const QStringList& fields, XQDatabaseData* data, const QString& Where , const QVariantList& WhereBindValues)
{
    if(data)
        return UPDATE(tableName, data->toVariantMap(false,fields), Where, WhereBindValues);
    return false;
}
bool XQMySql::DELETE(const QString& tableName, const QString& Where, QVariantList&& values)
{
    if (tableName.isEmpty())
        return false;
    QString sql =  QString("DELETE FROM `%1` ").arg(tableName);
    if (!Where.isEmpty())
    {
        if (!Where.contains("WHERE"))
            sql += "WHERE ";
        sql += Where;
    }
    sql = sql.trimmed();
    if (sql.back() != ';')
        sql += ";";
    return Query(sql, values);
}
bool XQMySql::tabelExists(const QString& tableName)
{
    if (Query(QString("SHOW TABLES LIKE '%1';").arg(tableName)) && result())
        return true;
    return false;
}
qint64 XQMySql::SELECT_COUNT(const QString& tableName, const QString& Where, const QVariantList& values )
{
    QString sql = QString("SELECT COUNT(*) FROM `%1` ").arg(tableName);
    if (!Where.isEmpty())
    {
        if (!Where.contains("WHERE"))
            sql += "WHERE ";
        sql += Where;
    }
    if(sql.back()!=';')
        sql += ";";
    if (!Query(sql,values))
        return 0;
   return result_oneRow()[0].toULongLong();

}
QStringList XQMySql::findData_toString(const QString& tableName, const QString& field)
{
    if (SELECT(tableName, {field}))
        return std::move(result_oneColumn_toString());
    return QStringList();
}
QList<QStringList> XQMySql::findDatas_toString(const QString& tableName, const QStringList& fields)
{
    if (SELECT(tableName, fields))
        return std::move(result_all_toString());
    return QList<QStringList>();
}
QList<QStringList> XQMySql::findDataAll_toString(const QString& tableName)
{
    if (SELECT(tableName, fieldList(tableName)))
        return std::move(result_all_toString());
    return QList<QStringList>();
}

QVariantList XQMySql::findData(const QString& tableName, const QString& field, const QString& Where, const QVariantList& values )
{
    if (SELECT_Where(tableName, { field }, Where,values))
        return std::move(result_oneColumn());
    return QVariantList();
}

QList<QVariantMap> XQMySql::findData_toVariantMap(const QString& tableName, const QString& field, const QString& Where, const QVariantList& values)
{
    auto data = findData(tableName,field, Where,values);
    QList<QVariantMap> mapList;
    for (auto& item : data)
        mapList << QVariantMap({ {field,item} });
    return mapList;
}
QList<QVariantList> XQMySql::findDatas(const QString& tableName, const QStringList& fields, const QString& Where, const QVariantList& values)
{
    if (SELECT_Where(tableName, fields, Where,values))
        return std::move(result_all());
    return QList<QVariantList>();
}
QList<QVariantMap> XQMySql::findDatas_toVariantMap(const QString& tableName, const QStringList& fields, const QString& Where, const QVariantList& values)
{
    auto data = findDatas(tableName,fields,Where,values);
    QList<QVariantMap> mapList;
    for (auto& row : data)
    {
        QVariantMap map;
        for (size_t i = 0; i < fields.size(); i++)
            map[fields[i]] = std::move(row[0]);
        mapList << std::move(map);
    }
    return mapList;
}
QList<QVariantList> XQMySql::findDataAll(const QString& tableName)
{
    if (SELECT(tableName, fieldList(tableName)))
        return std::move(result_all());
    return QList<QVariantList>();
}

QList<QStringList> XQMySql::result_all_toString()
{
    auto& query = SqlQuery();
    QList<QStringList>list;
    auto recordCnt = query.record().count();//获取列数
    while (query.next())
    {
        QStringList oneRow;//一行数据
        for (int i = 0; i < recordCnt; i++)
        {
            oneRow<< query.value(i).toString();
        }
        list<< oneRow;
    }
    return list;
}

QList<QVariantList> XQMySql::result_all()
{
    auto& query = SqlQuery();
    QList<QVariantList>list;
    auto recordCnt = query.record().count();//获取列数
    while (query.next())
    {
        QVariantList oneRow;//一行数据
        for (int i = 0; i < recordCnt; i++)
        {
            oneRow << query.value(i);
        }
        list << oneRow;
    }
    return list;
}

QStringList XQMySql::result_oneColumn_toString(int nSel)
{
    QStringList list;
    auto& query = SqlQuery();
    while (query.next())
    {
        list << query.value(nSel).toString();
    }
    return std::move(list);
}

QVariantList XQMySql::result_oneColumn(int nSel)
{
    QVariantList list;
    auto& query = SqlQuery();
    while (query.next())
    {
        list << query.value(nSel);
    }
    return std::move(list);
}

QStringList XQMySql::result_oneRow_toString()
{
    QStringList list;
    auto& query = SqlQuery();
    if (!query.next())
        return std::move (list);
    auto recordCnt = query.record().count();//获取列数
    for (int i = 0; i < recordCnt; i++)
    {
        list<< query.value(i).toString();
    }
    return std::move(list);
}

QVariantList XQMySql::result_oneRow()
{
    QVariantList list;
    auto& query = SqlQuery();
    if (!query.next())
        return std::move(list);
    auto recordCnt = query.record().count();//获取列数
    for (int i = 0; i < recordCnt; i++)
    {
        list << query.value(i);
    }
    return std::move(list);
}
QString XQMySql::result_one_toString()
{
    return result_one().toString();
}
QVariant  XQMySql::result_one()
{
    QVariant ret;
    result(ret);
    return std::move(ret);
}
QString XQMySql::error()const
{
    return std::move(m_sqlQuery.lastError().text());
}
int XQMySql::reconnectionCount() const
{
    return m_reconnectionCount;
}
int XQMySql::sleepTime() const
{
    return m_sleepTime;
}
void XQMySql::init()
{
    //installEventFilter(new XQFuncEventFilter(this));
   /* connect(qApp, &QApplication::aboutToQuit, this,&QObject::deleteLater );*/
    connect(this, &XQMySql::sqlError, [=](const QString& error) {XQMysqlInfo << "错误" << error; });
}
bool XQMySql::openDatabase(QSqlDatabase& database, int count, int sleep)
{
    int total = 0;
    auto it = m_hostPort.find(database.hostName());
    while (!database.open())
    {
        emit sqlError(QString("%1:%2 %3->打开数据库失败,暂停%4秒重新链接数据库,error:%5").arg(database.hostName()).arg(database.port()).arg(database.connectionName()).arg(sleep).arg( database.lastError().databaseText()));
        XQDelayEventLoop(sleep*1000);//暂停秒
        switch (database.lastError().type())
        {
        case QSqlError::NoError: break;
        case QSqlError::ConnectionError:
            if (it != m_hostPort.end())
                ++it;
            if (it == m_hostPort.end())
                it = m_hostPort.begin();
            if (it != m_hostPort.end())
            {
                database.setHostName(it.key()); database.setPort(it.value());
                emit sqlError(QString("%3->切换主机%1:%2").arg(database.hostName()).arg(database.port()).arg(database.connectionName()));
            }
            break;
        case QSqlError::StatementError:break;
        case QSqlError::TransactionError: break;
        case QSqlError::UnknownError: break;
        default:
            break;
        }
        if (count!=0&&++total >= count)
            break;
    }
    //设置超时时间

    return database.isOpen();
}
//void _insertStringList(QStringList& list, const char* data)
//{
//    _insertStringList(list, QString(data));
//}
//
//void _insertStringList(QStringList& list, const QString data)
//{
//    if(list.size()%2!=0)
//        list << QString("'%1'").arg(data);
//    else
//        list << QString("%1").arg(data);
//}
//
//void _insertStringListAll(QStringList& list, const char* data)
//{
//    _insertStringListAll(list, QString(data));
//}
//
//void _insertStringListAll(QStringList& list, const QString data)
//{
//    list << QString("'%1'").arg(data);
//}
